package com.hondee.fuzzyquery.utils;

/**
 * SQL SERVER 的SQL语句生成
 * @author Allen
 * @date 2014年8月28日
 */
public class SQLCreator {
	
	/**
	 * SQL SERVER 查询语句生成
	 * @param pageIndex
	 * @param pageSize
	 * @param tableName
	 * @param whereStr
	 * @param sortStr
	 * @return
	 * @author Allen
	 * @date 2014年4月1日
	 */
	public static String getSqlServerSql(int pageIndex,int pageSize,String tableName,String whereStr,String sortStr){
		Integer firstIndex = pageIndex*pageSize; 
		Integer lastIndex = pageSize*(pageIndex+1);
		
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" WITH query AS (SELECT inner_query.*,ROW_NUMBER() OVER (ORDER BY  CURRENT_TIMESTAMP) as ROWNO  FROM( select TOP ");
		sqlStr.append(lastIndex).append(" * from ").append(tableName).append(" where RecordValidity=1 ");
		if(!StringUtil.isNullOrEmpty(whereStr)){
			sqlStr.append(" and ").append(whereStr);
		}
		if(!StringUtil.isNullOrEmpty(sortStr)){
			sqlStr.append(" order by ").append(sortStr);
		}
		sqlStr.append(" ) inner_query ) SELECT * FROM query WHERE ROWNO >=").append(firstIndex+1)
		.append(" AND ROWNO < ").append(lastIndex==Integer.MAX_VALUE?Integer.MAX_VALUE:lastIndex+1);
		return sqlStr.toString();
	}
	
	public static String getSqlServerSqlNoPaging(String tableName,String fields,String whereStr,String sortStr){
		String sql = "SELECT "+(StringUtil.isNullOrEmpty(fields)?"*":fields)+" FROM "+tableName;
		if(!StringUtil.isNullOrEmpty(whereStr)){
			sql += " WHERE " + whereStr;
		}
		if(!StringUtil.isNullOrEmpty(sortStr)){
			sql += " ORDER BY " + sortStr;
		}
		return sql;	
	}
	
	public static String getSqlServerSqlWithColumns(int pageIndex,int pageSize,String tableName,String whereStr,String sortStr,String columns){
		Integer firstIndex = pageIndex*pageSize; 
		Integer lastIndex = pageSize*(pageIndex+1);
		
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" WITH query AS (SELECT inner_query.*,ROW_NUMBER() OVER (ORDER BY  CURRENT_TIMESTAMP) as ROWNO  FROM( select TOP ");
		sqlStr.append(lastIndex);
		if(StringUtil.isNullOrEmpty(columns)){
			sqlStr.append(" *");
		}else{
			sqlStr.append(" ").append(columns);
		}
		sqlStr.append(" from ").append(tableName).append(" where RecordValidity=1 ");
		if(!StringUtil.isNullOrEmpty(whereStr)){
			sqlStr.append(" and ").append(whereStr);
		}
		if(!StringUtil.isNullOrEmpty(sortStr)){
			sqlStr.append(" order by ").append(sortStr);
		}
		sqlStr.append(" ) inner_query ) SELECT * FROM query WHERE ROWNO >=").append(firstIndex+1)
		.append(" AND ROWNO < ").append(lastIndex==Integer.MAX_VALUE?Integer.MAX_VALUE:lastIndex+1);
		return sqlStr.toString();
	}
	/**
	 * SQL SERVER 数量合计语句生成
	 * @param tableName
	 * @param whereStr
	 * @return
	 * @author Allen
	 * @date 2014年4月1日
	 */
	public static String getSqlServerSqlCount(String tableName,String whereStr){
		StringBuffer sqlStr = new StringBuffer("select count(*) from ");
		sqlStr.append(tableName).append(" where  RecordValidity=1 ");
		if(!StringUtil.isNullOrEmpty(whereStr)){
			sqlStr.append("and ").append(whereStr);
		}
		return sqlStr.toString();
	}
}
